# encoding=utf-8
# 对数据库的操作都集中于此
# 3 beta2 (201108050741)
## 更新数据库版本
# 3 beta1 (201107222140)
## 更改数据库默认编码方式，使所有平台统一
## 创建数据库同时写入数据库版本信息
# Ver 2 beta1 (201107211900)
## 要求检查数据库版本
## 修改字串符编码方式
# Ver 1 beta2 (201107200239)
## 增加函数 GetTable()
import sqlite3
import os

def u(s):
    return s.decode('utf-8')

class CDatabase( ):
    def __init__( self, DatabaseFileName ):
        self._DbName = DatabaseFileName
        self._Connection = None
        self._Cursor = None
        self._CheckDb()

    def __del__(self):
        print( 'closing database...')
        self._Cursor.close()
        self._Connection.close()

    def _CheckDb(self):
        print('checking database...')
        if(not os.path.isfile(self._DbName)):
            print('The database is not existed')
            self._Connection = sqlite3.connect(self._DbName)
            self._Connection.text_factory = str
            self._CreateDB()
            self._Cursor = self._Connection.cursor()
            print('the database has created')
        else:
            print('the database is working')
            self._Connection = sqlite3.connect(self._DbName)
            self._Connection.text_factory = str
            self._Cursor = self._Connection.cursor()
        
        if not self._CheckDbTable('CONFIG'):
            print('the database is invalid')
        self._Cursor.execute('SELECT VERSION FROM CONFIG')
        if not self._Cursor.fetchall()[0][0] == 2:
            print('数据库版本与程序期望版本不符！')
            exit()
            return False

    def UpdateData(self):
        self._Connection.commit()

    def _CreateDB(self):
            self._Connection.execute('CREATE TABLE CONFIG(VERSION INTEGER)')
            self._Cursor = self._Connection.cursor()
            self._Cursor.execute('INSERT INTO CONFIG(VERSION) VALUES(%d)' % 2)
            self._Connection.execute('CREATE TABLE Artists(ID INTEGER PRIMARY KEY, Name VARCHER(25) UNIQUE)')
            self._Connection.execute('CREATE TABLE Pictures(FileID VARCHAR(64) PRIMARY KEY, ArtistID INTEGER, FileNameID INTEGER)')
 
    def _CheckDbTable(self, tableName):
        self._Cursor.execute('SELECT COUNT(*) FROM sqlite_master WHERE type="table" AND name="%s"' % u(tableName))
        if self._Cursor.fetchall()[0][0] <= 0:
            return False
        return True

    def NewPicture(self, ArtistName, FileID):
        print('checking %s' % ArtistName)
        self._Cursor.execute('SELECT (SELECT COUNT(*) FROM Pictures WHERE ArtistID=%d)cn1, (SELECT COUNT(*) FROM Pictures WHERE FileID="%s") cn2' % (self.GetArtistID(ArtistName), FileID))
        res = self._Cursor.fetchall()
        if res[0][1] <= 0:
            self._Cursor.execute('INSERT INTO Pictures(FileID, ArtistID, FileNameID) VALUES("%s", %d, "%d")' % (FileID, self.GetArtistID(ArtistName), res[0][0]))
            return res[0][0]
        return -1

    def GetArtistID(self, ArtistName):
        self._Cursor.execute('SELECT ID FROM Artists WHERE Name="%s"' % ArtistName)
        res = self._Cursor.fetchall()
        nLen = len(res)
        if nLen <= 0:
            self._Cursor.execute('INSERT INTO Artists(Name) VALUES("%s")' % ArtistName)
            self._Connection.commit()
            return self._Cursor.lastrowid
        return res[0][0]

    def GetArtistName(self, ArtistID):
        if(ArtistID == -1):
            self._Cursor.execute("SELECT Name FROM Artists")
            return self._Cursor.fetchall()
        self._Cursor.execute('SELECT Name FROM Artists WHERE ID = %d' % ArtistID)
        return self._Cursor.fetchall()[0][0]

    def GetTable( self, TableName ):
        self._Cursor.execute( 'SELECT * FROM %s' % TableName )
        return self._Cursor.fetchall() 

    def DeleteArtistByName(self, strName):
        ArtistID = self.GetArtistID(strName)
        self._Cursor.execute("DELETE FROM Pictures WHERE ArtistID = %d" % ArtistID)
        self._Cursor.execute("DELETE FROM Artists WHERE ID = %d" % ArtistID)